package hidalgo.expenses.dao;

import hidalgo.expenses.entities.Account;

import java.util.ArrayList;

import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;

public class AccountDao extends BaseDao {

	public static final String ACCOUNTID = "account_id";
	public static final String NAME = "name";
	public static final String USERID = "user_id";
	public static final String TOTAL = "total";// it's not a column

	public static Account getAccountById(int accountId) {
		MyHttpClient conection = new MyHttpClient(URLSELECT);

		conection.addDataToSend("queryphp",
				"select *, (select CASE (SUM(T.amount) IS NULL) "
						+ " WHEN 0 THEN SUM(T.amount) "
						+ " ELSE 0 END from transaction T "
						+ " where T.account_id=A.account_id) as total "
						+ " from account A " + "where A.account_id='"
						+ accountId + "' ");

		JSONObject jsonObjRecv = conection.executeHttpPost();

		if (jsonObjRecv != null) {
			try {
				JSONArray resultados = jsonObjRecv.getJSONArray("resultados");

				if (resultados != null) {

					Account account = null;

					for (int i = 0; i < resultados.length(); i++) {

						JSONObject elemento = (JSONObject) resultados.get(i);

						account = new Account();

						account.setAccountId(elemento.getInt(ACCOUNTID));
						account.setName(elemento.getString(NAME));
						account.setTotal(elemento.getDouble(TOTAL));
						account.setUserId(elemento.getInt(USERID));

					}

					resultados = null;

					return account;
				} else {
					return null;
				}
			} catch (JSONException e) {
				e.printStackTrace();
				return null;
			}
		} else {
			return null;
		}
	}

	public static ArrayList<Account> getAccountsByUser(int userId) {

		MyHttpClient conection = new MyHttpClient(URLSELECT);

		conection.addDataToSend("queryphp",
				"select *, (select CASE (SUM(T.amount) IS NULL) "
						+ " WHEN 0 THEN SUM(T.amount) "
						+ " ELSE 0 END from transaction T "
						+ " where T.account_id=A.account_id) as total "
						+ " from account A " + "where A.user_id='" + userId
						+ "' ");

		JSONObject jsonObjRecv = conection.executeHttpPost();

		if (jsonObjRecv != null) {
			try {
				JSONArray resultados = jsonObjRecv.getJSONArray("resultados");

				if (resultados != null) {

					ArrayList<Account> accountList = new ArrayList<Account>();

					for (int i = 0; i < resultados.length(); i++) {

						JSONObject elemento = (JSONObject) resultados.get(i);

						Account account = new Account();

						account.setAccountId(elemento.getInt(ACCOUNTID));
						account.setName(elemento.getString(NAME));
						account.setTotal(elemento.getDouble(TOTAL));
						account.setUserId(elemento.getInt(USERID));

						accountList.add(account);

					}

					resultados = null;

					return accountList;
				} else {
					return null;
				}
			} catch (JSONException e) {
				e.printStackTrace();
				return null;
			}
		} else {
			return null;
		}
	}

	public static boolean insert(Account account) {

		Boolean success = false;

		MyHttpClient conection = new MyHttpClient(URLINSERT_UPDATE_DELETE);

		conection.addDataToSend("queryphp", "INSERT INTO account " + "(" + NAME
				+ "," + USERID + ") " + " VALUES('" + account.getName() + "','"
				+ account.getUserId() + "')");

		JSONObject jsonObjRecv = conection.executeHttpPost();

		if (jsonObjRecv != null) {
			try {
				success = jsonObjRecv.getBoolean("resultados");
			} catch (JSONException e) {
				e.printStackTrace();
			}
		}

		return success;

	}

	public static boolean update(Account account) {

		Boolean success = false;

		MyHttpClient conection = new MyHttpClient(URLINSERT_UPDATE_DELETE);

		String sql = " UPDATE account SET " + NAME + "='" + account.getName()
				+ "'," + USERID + "='" + account.getUserId() + "' WHERE "
				+ ACCOUNTID + "= '" + account.getAccountId() + "' ";

		conection.addDataToSend("queryphp", sql);

		JSONObject jsonObjRecv = conection.executeHttpPost();

		if (jsonObjRecv != null) {
			try {

				success = jsonObjRecv.getBoolean("resultados");

				return success;
			} catch (JSONException e) {
				e.printStackTrace();
				return success;
			}
		} else {
			return success;
		}

	}

	public static Boolean deleteById(int accountId) {
		Boolean success = false;

		MyHttpClient conection = new MyHttpClient(URLINSERT_UPDATE_DELETE);

		conection.addDataToSend("queryphp", "DELETE FROM account WHERE "
				+ ACCOUNTID + "='" + accountId + "' ");

		JSONObject jsonObjRecv = conection.executeHttpPost();

		if (jsonObjRecv != null) {
			try {

				success = jsonObjRecv.getBoolean("resultados");

				return success;
			} catch (JSONException e) {
				e.printStackTrace();
				return success;
			}
		} else {
			return success;
		}
	}

}